﻿-- CREATE TRIGGER trgchange_project_info
--   AFTER INSERT OR UPDATE or DELETE
--   ON kdvn_project
--   FOR EACH ROW
--   EXECUTE PROCEDURE trgfnupdate_project_to_contract();

CREATE OR REPLACE FUNCTION trgfnupdate_project_to_contract()
                        RETURNS trigger AS
                        $BODY$
                        DECLARE
				using_id integer;
				old_id integer;
                        Begin
                        -- ### Begin Update Trigger ###
                            If TG_OP='UPDATE' then
                                if 
					(new.contract_id<>old.contract_id) or 
					(new.contract_id is null and old.contract_id>0) or
					(new.contract_id >0 and old.contract_id is null) or
					(old.project_status<>new.project_status) or
					(old.project_amount<>new.project_amount) or  
					(old.project_vat<>new.project_vat) or
					(new.owner_id<>old.owner_id) or
					(new.client_id<>old.client_id) or
					(new.address_id<>old.address_id) or
					(new.invoice_address_id<>old.invoice_address_id)
                                then
                                -- ## Update New Contract ID ## --
                                	using_id = new.contract_id;
					old_id = old.contract_id;
					update 
						kdvn_contract_client 
					Set 
						owner_id=(select owner_id from kdvn_project where project_status<>'cancel' and contract_id=using_id limit 1),
						client_id=(select client_id from kdvn_project where project_status<>'cancel' and contract_id=using_id limit 1),
						address_id=(select address_id from kdvn_project where project_status<>'cancel' and contract_id=using_id limit 1),
						invoice_address_id=(select invoice_address_id from kdvn_project where project_status<>'cancel' and contract_id=using_id limit 1),
						contract_amount_e=(Select coalesce(sum(project_amount),0) from kdvn_project where project_type='E' and project_status<>'cancel' and contract_id=using_id),
						contract_amount_m=(Select sum(project_amount) from kdvn_project where project_type='M' and project_status<>'cancel' and contract_id=using_id),
						contract_vat=(Select sum(project_vat) from kdvn_project where project_status<>'cancel' and contract_id=using_id),
						contract_total=(Select sum(project_total) from kdvn_project where project_status<>'cancel' and contract_id=using_id)
					where id=using_id;
				-- ## Update old Contract ID ## --
					update 
						kdvn_contract_client 
					Set
						owner_id=(select owner_id from kdvn_project where project_status<>'cancel' and contract_id=old_id limit 1),
						client_id=(select client_id from kdvn_project where project_status<>'cancel' and contract_id=old_id limit 1),
						address_id=(select address_id from kdvn_project where project_status<>'cancel' and contract_id=old_id limit 1),
						invoice_address_id=(select invoice_address_id from kdvn_project where project_status<>'cancel' and contract_id=old_id limit 1),
						
						contract_amount_e=(Select sum(project_amount) from kdvn_project where project_type='E' and project_status<>'cancel' and contract_id=old_id),
						contract_amount_m=(Select sum(project_amount) from kdvn_project where project_type='M' and project_status<>'cancel' and contract_id=old_id),
						contract_vat=(Select sum(project_vat) from kdvn_project where project_status<>'cancel' and contract_id=old_id),
						contract_total=(Select sum(project_total) from kdvn_project where project_status<>'cancel' and contract_id=old_id)
					where id=old_id;
					
					Update 
						kdvn_project 
					set 
						project_currency_reference=(Select id from project_currency where default_curr=True and contract_id=using_id limit 1),
						project_exrate_reference=(Select coalesce(rate,0) from project_currency where default_curr=True and contract_id=using_id limit 1)
					where
						contract_id = using_id;
                                end if;
			--end if;
                        -- ### End Update Trigger ###
			Return New;
                        -- ### Begin Insert Trigger ###
		    ELSEIf TG_OP='INSERT' then
				using_id = new.contract_id;
				if using_id>0 then
                                -- ## Insert New Contract ID ## --
					update 
						kdvn_contract_client 
					Set 
						owner_id=(select owner_id from kdvn_project where owner_id>0 and project_status<>'cancel' and contract_id=using_id limit 1),
						client_id=(select client_id from kdvn_project where client_id>0 and project_status<>'cancel' and contract_id=using_id limit 1),
						address_id=(select address_id from kdvn_project where address_id>0 and project_status<>'cancel' and contract_id=using_id limit 1),
						invoice_address_id=(select invoice_address_id from kdvn_project where invoice_address_id>0 and project_status<>'cancel' and contract_id=using_id limit 1),
						contract_amount_e=(Select sum(coalesce(project_amount,0)) from kdvn_project where project_type='E' and project_status<>'cancel' and contract_id=using_id),
						contract_amount_m=(Select sum(coalesce(project_amount,0)) from kdvn_project where project_type='M' and project_status<>'cancel' and contract_id=using_id),
						contract_vat=(Select sum(coalesce(project_vat,0)) from kdvn_project where project_status<>'cancel' and contract_id=using_id),
						contract_total=(Select sum(coalesce(project_total,0)) from kdvn_project where project_status<>'cancel' and contract_id=using_id)
					where id=using_id;

					Update 
						kdvn_project 
					set 
						project_currency_reference=(Select id from project_currency where default_curr=True and contract_id=using_id limit 1),
						project_exrate_reference=(Select coalesce(rate,0) from project_currency where default_curr=True and contract_id=using_id limit 1)
					where
						contract_id = using_id;
			Return New;
		end if;
		 -- ### End Insert Trigger ###
                        -- ### Begin Delete Trigger ###
	ELSEIf TG_OP='DELETE' then
				using_id = old.contract_id;
                                if old.project_status<>'cancel' then
                                -- ## Insert Delete Contract ID ## --
					update 
						kdvn_contract_client 
					Set 
						owner_id=(select owner_id from kdvn_project where project_status<>'cancel' and contract_id=using_id limit 1),
						client_id=(select client_id from kdvn_project where project_status<>'cancel' and contract_id=using_id limit 1),
						address_id=(select address_id from kdvn_project where project_status<>'cancel' and contract_id=using_id limit 1),
						invoice_address_id=(select invoice_address_id from kdvn_project where project_status<>'cancel' and contract_id=using_id limit 1),
						contract_amount_e=(Select sum(project_amount) from kdvn_project where project_type='E' and project_status<>'cancel' and contract_id=using_id),
						contract_amount_m=(Select sum(project_amount) from kdvn_project where project_type='M' and project_status<>'cancel' and contract_id=using_id),
						contract_vat=(Select sum(project_vat) from kdvn_project where project_status<>'cancel' and contract_id=using_id),
						contract_total=(Select sum(project_total) from kdvn_project where project_status<>'cancel' and contract_id=using_id)
					where id=using_id;
			end if;
			return Old;
		end if;
		 -- ### End Delete Trigger ###		 
		RETURN NULL;
                        end;    
                        $BODY$
                      LANGUAGE plpgsql VOLATILE
                      COST 100;